/*
 * DBSistema
 * 
 * Class that will take care of operations on the DB Sistema
 * 
 * author Luigi di Martino , Carmine Colicino
 * 
 * version 1.0
 */

package it.unisa.udocs.storage;

import it.unisa.udocs.commons.DBConnectionPool;
import it.unisa.udocs.commons.DBNames;
import it.unisa.udocs.data.Account;
import it.unisa.udocs.data.AreaScientifica;
import it.unisa.udocs.data.Dipartimento;
import it.unisa.udocs.data.Evento;
import it.unisa.udocs.data.ResponsabileAteneo;
import it.unisa.udocs.data.Ricercatore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.Statement;

public class DBSistema {


	private static DBSistema dbSistema;
	
	/**
	 * Method that implements the Singleton Design Pattern
	 * 
	 * @return DBSistema
	 * 
	 */
	public static DBSistema getInstance() {
		if (dbSistema == null)
			dbSistema = new DBSistema();
		return dbSistema;
	}
	
	/**
	 * It's used to search for all Departments 
	 * 
	 *  @return A list containing all departments 
	 * 
	 */
	public ArrayList<Dipartimento> getAllDipart() throws SQLException{
		
		ArrayList<Dipartimento> list = new ArrayList<Dipartimento>();
		

		Connection con= DBConnectionPool.getConnection();
		Dipartimento dipart=null;
		String query="SELECT * FROM dipartimento" ;
		
		Statement st=null;
		ResultSet rs=null;

		st = (Statement) con.createStatement();
		rs=st.executeQuery(query);

		while(rs.next()) {
			dipart= new Dipartimento (rs.getInt(DBNames.DIPART_ID),
					rs.getString(DBNames.DIPART_NOME));
			list.add(dipart);
			
		}
		
		rs.close();
		st.close();
		DBConnectionPool.releaseConnection(con);
		
		return list;
	}
	
	/**
	 *  It's used to search for all Scientific Areas
	 * 
	 *  @return A list containing all Scientific Areas 
	 * 
	 */
	public ArrayList<AreaScientifica> getAllAree() throws SQLException{
		
		ArrayList<AreaScientifica> list = new ArrayList<AreaScientifica>();
		

		Connection con= DBConnectionPool.getConnection();
        AreaScientifica area=null;
		String query="SELECT * FROM area_scientifica" ;
		
		Statement st=null;
		ResultSet rs=null;

		st = (Statement) con.createStatement();
		rs=st.executeQuery(query);

		while(rs.next()) {
			area= new AreaScientifica (rs.getInt(DBNames.AREA_SCI_ID),
					rs.getString(DBNames.AREA_SCI_NOME),rs.getString(
					DBNames.AREA_SCI_COD));
			list.add(area);
			
		}
		rs.close();
		st.close();
		DBConnectionPool.releaseConnection(con);
		
		return list;
	}
	
	/**
	 * It's used to search for all available Events 
	 * 
	 *  @return A list containing all available Events 
	 * 
	 */
	public ArrayList<Evento> getEventiDisponibili() {
		
		ArrayList<Evento> listaEventiDisponibili = new ArrayList<Evento>();
		
		Evento eventoDisponibile = null;
		
		try {
			//Crea una connessione al DB
        	Connection con = DBConnectionPool.getConnection(); 
        	
        	 String query = "SELECT * FROM Evento WHERE evento.nome NOT IN "
        	 		+ "(SELECT evento.nome FROM Evento JOIN Responsabile_ateneo"
        	 		+ " ON Evento.id_evento = Responsabile_ateneo.evento )";
        	 
        	 Statement st = (Statement) con.createStatement();
        	 ResultSet rs = st.executeQuery(query);
        	 
        	 while(rs.next()) {
        		 eventoDisponibile = new Evento(rs.getInt(DBNames.EVT_ID),
        				 					rs.getString(DBNames.EVT_NAME),
        				 					rs.getDate(DBNames.EVT_DATA_INIZIO),
        				 					rs.getDate(DBNames.EVT_DATA_FINE),
        				 					rs.getString(DBNames.EVT_BANDO),
        				 					rs.getString(DBNames.EVT_URL)
        				 					);
        		 
        		 listaEventiDisponibili.add(eventoDisponibile);
        	 }
        	 
             rs.close();
             st.close();
             DBConnectionPool.releaseConnection(con);
		} catch(SQLException e) {
			e.printStackTrace();
		}
		
		return listaEventiDisponibili;
	}
	
	/**
	 * it's used to store a new Account in the system
	 * 
	 * @param account - The account 
	 * 
	 *  @return The Account'id  
	 * 
	 */
	public int creaAccount(Account account) {
		
		int idAccount = 0;
		
		try {
			//Crea una connessione al DB
        	Connection con = DBConnectionPool.getConnection(); 
        	
        	 String query = "INSERT INTO account VALUES (?,?,?,?)";
             
             PreparedStatement st = con.prepareStatement(query);
             
             st.setInt(1, 0);
             st.setString(2, account.getUsername());
             st.setString(3,account.getPassword());
             st.setString(4, account.getTipologia());
             
             st.executeUpdate();
             con.commit();
             
             query = "SELECT id_account FROM account WHERE username = '" 
            		 + account.getUsername() + "' AND password = '" 
            		 + account.getPassword() + "'";
             
             Statement stmt = (Statement) con.createStatement();
             ResultSet rs = stmt.executeQuery(query);
             if(rs.next()) {
            	 idAccount = Integer.parseInt(rs.getString(DBNames.ACC_ID));
             }
             
             rs.close();
             st.close();
             stmt.close();
             DBConnectionPool.releaseConnection(con);
		} catch(SQLException e) {
			e.printStackTrace();
		}
		
		return idAccount;
	}
          
	/**
	 * it's used to store a researcher in the system
	 * 
	 * @param account - The Researcher's account
	 * 
	 */
	public void creaRicercatore(Account account) {
		
		Ricercatore ricercatore = (Ricercatore) account; 
		
		try {
        	//Crea una connessione al DB
        	Connection con = DBConnectionPool.getConnection();

            //Create la query di inserimento
            String query = "INSERT INTO Ricercatore VALUES (?,?,?,?,?,?,?,?,?,"
            		+ "?,?,?,?,?)";
            
            PreparedStatement st = con.prepareStatement(query);
            
            st.setInt(1, ricercatore.getId_account());
            st.setString(2, ricercatore.getNome());
            st.setString(3, ricercatore.getCognome());
            st.setString(4,ricercatore.getCodice_fiscale());
            st.setDate(5, ricercatore.getData_nascita());
            st.setString(6,ricercatore.getCitta_nascita());
            st.setString(7,ricercatore.getSesso());
            st.setString(8,ricercatore.getIndirizzo());
            st.setString(9, ricercatore.getEmail());
            st.setString(10,ricercatore.getRuolo());
            st.setString(11, ricercatore.getMatricola());
            st.setInt(12, ricercatore.getAnno_messa_in_ruolo());
            st.setInt(13,ricercatore.getDipartimento());
            st.setInt(14,ricercatore.getAreaScientifica());
            
            st.executeUpdate();
            con.commit();
            
            st.close();
            DBConnectionPool.releaseConnection(con);
        } catch (SQLException e) {
        	e.printStackTrace();
        }		
	}
	
	/**
	 *  it's used to store an University's Responsible 
	 * 
	 * @param account - The Responsible's account
	 * 
	 */
	public void creaResponsabileAteneo(Account account) {
		
		ResponsabileAteneo responsabile = (ResponsabileAteneo) account;
		
		try {
        	//Crea una connessione al DB
        	Connection con = DBConnectionPool.getConnection();

            //Create la query di inserimento
            String query = "INSERT INTO Responsabile_ateneo VALUES (?,?,?,?)";
            
            PreparedStatement st = con.prepareStatement(query);
            
            st.setInt(1, responsabile.getId_account());
            st.setString(2, responsabile.getNome());
            st.setString(3, responsabile.getCognome());
            st.setInt(4,responsabile.getEvento());
            
            st.executeUpdate();
            con.commit();
            
            st.close();
            DBConnectionPool.releaseConnection(con);
        } catch (SQLException e) {
        	e.printStackTrace();
        }
	}
	
	/**
	 * it's used to search  for an Account
	 * 
	 * @param campiCompilati -  An Array containing information about the Account what we are searching for
	 * 
	 *  @return A list of Account 
	 * 
	 */
	public ArrayList<Account> cercaAccount(ArrayList<String> campiCompilati) {
		//Istanzia un oggetto account
		Account account = null;
				
		//Istanzia un ArrayList di Account
		ArrayList<Account> listaAccount = new ArrayList<Account>();
				
		try {
			//Crea una connessione al DB
			Connection con = DBConnectionPool.getConnection();

			//Crea la query di ricerca
			String query = "SELECT * FROM Account JOIN Ricercatore ON "
					+ "Account.id_account = Ricercatore.id_account "
					+ "WHERE nome LIKE '%" + campiCompilati.get(0) + "%'"
					+ " AND cognome LIKE '%" + campiCompilati.get(1) + "%'"
					+ " AND matricola LIKE '%" + campiCompilati.get(2) + "%'";

			System.out.println(query);
			Statement st = (Statement) con.createStatement();

			ResultSet rs = st.executeQuery(query);

			while(rs.next())
			{
				account = new Ricercatore(
						Integer.parseInt(rs.getString(DBNames.ACC_ID)),
						rs.getString(DBNames.ACC_USER),
						rs.getString(DBNames.ACC_PSW),
						rs.getString(DBNames.ACC_TIPOLOGIA),
						rs.getString(DBNames.RICER_NOME),
						rs.getString(DBNames.RICER_COGN),
						rs.getString(DBNames.RICER_COD_FISC),
						rs.getDate(DBNames.RICER_DATA_NASC),
						rs.getString(DBNames.RICER_CITTA_NASC),
						rs.getString(DBNames.RICER_SEX),
						rs.getString(DBNames.RICER_INDIRIZZO),
						rs.getString(DBNames.RICER_EMAIL),
						rs.getString(DBNames.RICER_RUOLO),
						rs.getString(DBNames.RICER_MATRIC),
						rs.getInt(DBNames.RICER_ANNO_MES_RUOLO),
						rs.getInt(DBNames.RICER_DIPART),
						rs.getInt(DBNames.RICER_AREA_SCI));
				
				System.out.println("id: "+account.getId_account() 
						+ " tipologia: " + account.getTipologia());
				listaAccount.add(account);
			}
			
			System.out.println("List_Account created");

			st.close();
			rs.close();
			DBConnectionPool.releaseConnection(con);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listaAccount;
	}
	
	/**
	 * it's used to search for an Account specifying its ID 
	 * 
	 * @param id - The Account's ID 
	 * 
	 *  @return A list containing The account found 
	 * 
	 */
	public ArrayList<Account> getAccountById(int id) {
		int id_account = id;
		
		//Istanzia un oggetto account
		Account account = null;
				
		//Istanzia un ArrayList di Account
		ArrayList<Account> listaAccount = new ArrayList<Account>();
		
		try {
			//Crea una connessione al DB
			Connection con = DBConnectionPool.getConnection();

			//Crea la query di ricerca
			String query = "SELECT * FROM Account JOIN Ricercatore "
					+ "ON Account.id_account = Ricercatore.id_account "
					+ "WHERE Account.id_account = " + id_account + "";
			
			System.out.println(query);
			Statement st = (Statement) con.createStatement();

			ResultSet rs = st.executeQuery(query);

			while(rs.next())
			{
				account = new Ricercatore(
						Integer.parseInt(rs.getString(DBNames.ACC_ID)),
						rs.getString(DBNames.ACC_USER),
						rs.getString(DBNames.ACC_PSW),
						rs.getString(DBNames.ACC_TIPOLOGIA),
						rs.getString(DBNames.RICER_NOME),
						rs.getString(DBNames.RICER_COGN),
						rs.getString(DBNames.RICER_COD_FISC),
						rs.getDate(DBNames.RICER_DATA_NASC),
						rs.getString(DBNames.RICER_CITTA_NASC),
						rs.getString(DBNames.RICER_SEX),
						rs.getString(DBNames.RICER_INDIRIZZO),
						rs.getString(DBNames.RICER_EMAIL),
						rs.getString(DBNames.RICER_RUOLO),
						rs.getString(DBNames.RICER_MATRIC),
						rs.getInt(DBNames.RICER_ANNO_MES_RUOLO),
						rs.getInt(DBNames.RICER_DIPART),
						rs.getInt(DBNames.RICER_AREA_SCI));
				
				listaAccount.add(account);
			}

//			System.out.println("List_Product created" );
			
			st.close();
			rs.close();
			DBConnectionPool.releaseConnection(con);
			} catch (SQLException e) {
			e.printStackTrace();
		}

		return listaAccount;
		
	}
	
	/**
	 * It's used to modify an Account
	 * 
	 * @param newAccount - The New Account
	 * 
	 */
	public void modificaAccount(Account account) {
		
		Ricercatore ricercatore = (Ricercatore) account;
		
		try {
			//Crea una connessione al DB
			Connection con = DBConnectionPool.getConnection();
			
			int id = account.getId_account();

			String query="UPDATE Account INNER JOIN Ricercatore "
					+ "ON Account.id_account = Ricercatore.id_account "
					+ "SET username = ?,password = ?,nome = ?,cognome = ?,"
					+ "codice_fiscale = ?,data_nascita = ?,citta_nascita = ?,"
					+ "sesso = ?,indirizzo = ?,email = ?,ruolo = ?,"
					+ "matricola = ?,anno_messa_in_ruolo = ?,dipartimento = ?,"
					+ "area_scientifica = ? WHERE Account.id_account = ?";
			
			PreparedStatement st = con.prepareStatement(query);
			
			st.setString(1, ricercatore.getUsername());
			st.setString(2, ricercatore.getPassword());
			st.setString(3, ricercatore.getNome());
			st.setString(4, ricercatore.getCognome());
			st.setString(5, ricercatore.getCodice_fiscale());
			st.setDate(6, ricercatore.getData_nascita());
			st.setString(7, ricercatore.getCitta_nascita());
			st.setString(8, ricercatore.getSesso());
			st.setString(9, ricercatore.getIndirizzo());
			st.setString(10, ricercatore.getEmail());
			st.setString(11, ricercatore.getRuolo());
			st.setString(12, ricercatore.getMatricola());
			st.setInt(13, ricercatore.getAnno_messa_in_ruolo());
			st.setInt(14, ricercatore.getDipartimento());
			st.setInt(15, ricercatore.getAreaScientifica());
			st.setInt(16, id);
			
			st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * It's used to remove an account from the storage 
	 * 
	 * @param account - The Account
	 * 
	 */
	public void eliminaAccount(Account account) {
		try {
			//Crea una connessione al DB
			Connection con = DBConnectionPool.getConnection();
			
			int id = account.getId_account();

			String query="DELETE FROM Account WHERE id_account = ?";
			
			PreparedStatement st = con.prepareStatement(query);
            
            st.setInt(1, id);
            
            st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * It's used to store a new Department in the system
	 * 
	 * @param dipartimento - The department 
	 * 
	 */
	public void creaDipartimento(Dipartimento dip){
		try{
			Connection con = DBConnectionPool.getConnection();
			
			String query = "INSERT INTO dipartimento VALUES (?,?)";
			
			PreparedStatement st  = con.prepareStatement(query);
			
			st.setInt(1,0);
			st.setString(2, dip.getNomeDip());
			
			st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);
		}catch(SQLException e) {
		    e.printStackTrace();
		}
		
		
	}
	
	/**
	 * it's used to search for a Department
	 * 
	 * @param nome - The Department's name
	 * 
	 *  @return A list containing all Departments found 
	 * 
	 */
	public ArrayList<Dipartimento> cercaDipartimento(String nomeDip){
		try{
			Connection con = DBConnectionPool.getConnection();
			
			String query = "SELECT * FROM Dipartimento "
					+ "WHERE nome_dipartimento LIKE '%"+nomeDip+"%'";
			ArrayList<Dipartimento> listaDip = new ArrayList<Dipartimento>();
			PreparedStatement st = con.prepareStatement(query);
			ResultSet rs = st.executeQuery();
			Dipartimento dipTemp = null ;
			
			while(rs.next()) {
				dipTemp  = new Dipartimento(rs.getInt(DBNames.DIPART_ID),
						rs.getString(DBNames.DIPART_NOME));
				listaDip.add(dipTemp);
			}
			
			rs.close();
			st.close();
			DBConnectionPool.releaseConnection(con);
			return listaDip;
			
		}catch(SQLException e ) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * it's used to search for a specific Department specifying its ID 
	 * 
	 * @param id - The Department's id
	 * 
	 *  @return A list containing The Department found 
	 * 
	 */
	public ArrayList<Dipartimento> getDipartimentoById(int id) {
		int id_dip = id;
		
		//Istanzia un oggetto account
		Dipartimento dipartimento = null;
				
		//Istanzia un ArrayList di Account
		ArrayList<Dipartimento> listaDipartimenti = 
				new ArrayList<Dipartimento>();
		
		try {
			//Crea una connessione al DB
			Connection con = DBConnectionPool.getConnection();

			//Crea la query di ricerca
			String query = "SELECT * FROM Dipartimento "
					+ "WHERE id_dipartimento = " + id_dip + "";
			
			System.out.println(query);
			Statement st = (Statement) con.createStatement();

			ResultSet rs = st.executeQuery(query);

			while(rs.next())
			{
				dipartimento = new Dipartimento(
						Integer.parseInt(rs.getString(DBNames.DIPART_ID)),
						rs.getString(DBNames.DIPART_NOME));
				
				listaDipartimenti.add(dipartimento);
			}

//			System.out.println("List_Product created" );
			
			st.close();
			rs.close();
			DBConnectionPool.releaseConnection(con);
			} catch (SQLException e) {
			e.printStackTrace();
		}

		return listaDipartimenti;
		
	}
	
	/**
	 * it's used to modify a specific Department 
	 * 
	 * @param newDipartimento - The new Department
	 * 
	 */
	public void modificaDipartimento(Dipartimento dip) {
		
		try{
			Connection con = DBConnectionPool.getConnection();
			int idDip = dip.getId_dipartimento();
			String query = "UPDATE dipartimento set nome_dipartimento=? WHERE id_dipartimento ="+idDip+"";
			PreparedStatement st = con.prepareStatement(query);

			st.setString(1,dip.getNomeDip());
			
			st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);			
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * It's used to remove from our storage a specific Department
	 * 
	 * @param dipartimento - The Department 
	 * 
	 */
	public void eliminaDipartimento(Dipartimento dipartimento) {
		try {
			//Crea una connessione al DB
			Connection con = DBConnectionPool.getConnection();
			
			int id = dipartimento.getId_dipartimento();

			String query="DELETE FROM Dipartimento WHERE id_dipartimento = ?";
			
			PreparedStatement st = con.prepareStatement(query);
            
            st.setInt(1, id);
            
            st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * This method is used to create a scientific area
	 * 
	 * @param area - The new Scientific area
	 * 
	 */
	public void creaAreaScientifica(AreaScientifica area) {
		
		try{
			Connection con = DBConnectionPool.getConnection();
			String query = "INSERT INTO area_scientifica VALUES (?,?,?)";
			
			PreparedStatement st  = con.prepareStatement(query);
			
			st.setInt(1,0);
			st.setString(2, area.getNome());
			st.setString(3,area.getCodiceArea());
			
			st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);
			
		} catch(SQLException e) {
			e.printStackTrace();
		}
		
		
		
	}
	
	/**
	 * It's used to search for a Scientific Area
	 * 
	 * @param nome - The Scientific Area's name
	 * @param codice - The Scientific Area's code
	 * 
	 *  @return A list with The Scientific Area found
	 * 
	 */
	public ArrayList<AreaScientifica> cercaAreaScientifica(String nome , String codice) {
		
		try{
			Connection con = DBConnectionPool.getConnection();
			
			String query = " SELECT * FROM area_scientifica "
					+ "where nome_area_scientifica LIKE '%"+nome+"%' AND "
					+ "codice_area_scientifica LIKE '%"+codice+"%' ";
			
			ArrayList<AreaScientifica> lista = new ArrayList<AreaScientifica>();
			PreparedStatement st = con.prepareStatement(query);
			ResultSet rs = st.executeQuery();
			AreaScientifica areaTemp = null ;
			
			while(rs.next()) {
				areaTemp  = new AreaScientifica(rs.getInt(DBNames.AREA_SCI_ID),
						rs.getString(DBNames.AREA_SCI_NOME),
						rs.getString(DBNames.AREA_SCI_COD));
				lista.add(areaTemp);
			}
			
			rs.close();
			st.close();
			DBConnectionPool.releaseConnection(con);
			return lista;
			
		}catch(SQLException e ) {
			e.printStackTrace();
			return null;
		}
		
	}
	
	/**
	 * It's used to search for a Scientific Area specifying its ID 
	 * 
	 * @param id - The Scientific Area's Id 
	 * 
	 *  @return A list with The Scientific Area found
	 * 
	 */
	public ArrayList<AreaScientifica> getAreaScientificaById(int id) {
		int id_area = id;
		
		//Istanzia un oggetto account
		AreaScientifica area = null;
				
		//Istanzia un ArrayList di Account
		ArrayList<AreaScientifica> listaAreeScientifiche = 
				new ArrayList<AreaScientifica>();
		
		try {
			//Crea una connessione al DB
			Connection con = DBConnectionPool.getConnection();

			//Crea la query di ricerca
			String query = "SELECT * FROM Area_scientifica "
					+ "WHERE id_area_scientifica = " + id_area + "";
			
			System.out.println(query);
			Statement st = (Statement) con.createStatement();

			ResultSet rs = st.executeQuery(query);

			while(rs.next())
			{
				area = new AreaScientifica(
						Integer.parseInt(rs.getString(DBNames.AREA_SCI_ID)),
						rs.getString(DBNames.AREA_SCI_NOME),
						rs.getString(DBNames.AREA_SCI_COD));
				
				listaAreeScientifiche.add(area);
			}

//			System.out.println("List_Product created" );
			
			st.close();
			rs.close();
			DBConnectionPool.releaseConnection(con);
			} catch (SQLException e) {
			e.printStackTrace();
		}

		return listaAreeScientifiche;
		
	}
	
	/**
	 * It's used to modify a Scientific Area
	 * 
	 * @param area - The new Scientific Area
	 * 
	 */
	public void modificaAreaScientifica(AreaScientifica area){
		try{
			Connection con = DBConnectionPool.getConnection();
			
			int id = area.getId_area();

			String query = "UPDATE area_scientifica set nome_area_scientifica=?,"
					+ " codice_area_scientifica=? WHERE id_area_scientifica ="
					+id+"";

			PreparedStatement st = con.prepareStatement(query);
			
			st.setString(1, area.getNome());
			st.setString(2,area.getCodiceArea());
			st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);

		}catch(SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * It's used to delete a Scientific Area from the system
	 * 
	 * @param The Scientific Area to delete
	 * 
	 */
	public void eliminaAreaScientifica(AreaScientifica areaScientifica) {
		try {
			//Crea una connessione al DB
			Connection con = DBConnectionPool.getConnection();
			
			int id = areaScientifica.getId_area();

			String query="DELETE FROM Area_scientifica "
					+ "WHERE id_area_scientifica = ?";
			
			PreparedStatement st = con.prepareStatement(query);
            
            st.setInt(1, id);
            
            st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * It's used to store an Event in the System
	 * 
	 * @param evt - The Event
	 * 
	 */
	public void creaEvento(Evento evt) {
		
		try{
			Connection con = DBConnectionPool.getConnection();
			String query = "INSERT INTO evento VALUES (?,?,?,?,?,?)";
			
			PreparedStatement st  = con.prepareStatement(query);
			
			st.setInt(1,0);
			st.setString(2, evt.getNomeEvt());
			st.setDate(3, evt.getDataInizio());
		    st.setDate(4, evt.getDataFine());
		    st.setString(5, evt.getBando());
		    st.setString(6, evt.getUrl());
		    
			st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);
			
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * This method is used to search for an Event. 
	 * 
	 * @param nome - The Event's name
	 * @param bando - The Event's competition 
	 * 
	 *  @return The list of All Events found  
	 * 
	 */
	public ArrayList<Evento> cercaEvento(String nome, String bando) {
		
		try{
			Connection con = DBConnectionPool.getConnection();
			
			String query = " SELECT * FROM Evento where nome LIKE '%"
					+nome+"%' AND bando LIKE '%"+bando+"%' ";
			
			ArrayList<Evento> lista = new ArrayList<Evento>();
			PreparedStatement st = con.prepareStatement(query);
			ResultSet rs = st.executeQuery();
			Evento eventoTemp = null ;
			
			while(rs.next()) {
				eventoTemp  = new Evento(rs.getInt(DBNames.EVT_ID), 
						rs.getString(DBNames.EVT_NAME), 
						rs.getDate(DBNames.EVT_DATA_INIZIO),
						rs.getDate(DBNames.EVT_DATA_FINE), 
						rs.getString(DBNames.EVT_BANDO), 
						rs.getString(DBNames.EVT_URL));
				lista.add(eventoTemp);
			}
			
			rs.close();
			st.close();
			DBConnectionPool.releaseConnection(con);
			return lista;
			
		}catch(SQLException e ) {
			e.printStackTrace();
			return null;
		}
		
	}
	
	/**
	 * This method search for an event specifying its  id
	 * 
	 * @param id - The Event's id 
	 * 
	 *  @return  The Event found  
	 * 
	 */
	public ArrayList<Evento> getEventoById(int id) {
		int id_evento = id;
		
		//Istanzia un oggetto account
		Evento evento = null;
				
		//Istanzia un ArrayList di Account
		ArrayList<Evento> listaEventi = new ArrayList<Evento>();
		
		try {
			//Crea una connessione al DB
			Connection con = DBConnectionPool.getConnection();

			//Crea la query di ricerca
			String query = "SELECT * FROM Evento WHERE id_evento = " 
					+ id_evento + "";
			
			System.out.println(query);
			Statement st = (Statement) con.createStatement();

			ResultSet rs = st.executeQuery(query);

			while(rs.next())
			{
				evento = new Evento(
						rs.getInt(DBNames.EVT_ID),
						rs.getString(DBNames.EVT_NAME),
						rs.getDate(DBNames.EVT_DATA_INIZIO),
						rs.getDate(DBNames.EVT_DATA_FINE),
						rs.getString(DBNames.EVT_BANDO),
						rs.getString(DBNames.EVT_URL));
				
				listaEventi.add(evento);
			}

//			System.out.println("List_Product created" );
			
			st.close();
			rs.close();
			DBConnectionPool.releaseConnection(con);
			} catch (SQLException e) {
			e.printStackTrace();
		}

		return listaEventi;
		
	}
		
	/**
	 * It's used to modify an Event
	 * 
	 * @param evt - The Event to modify
	 * 
	 */
	public void modificaEvento(Evento evt) {
		try{
			int idEvt= evt.getId_evento();
			
			Connection con = DBConnectionPool.getConnection();
			String query = "UPDATE evento set nome=?, data_inizio=?, "
					+ "data_fine=?, bando=?, url=? WHERE id_evento ="
					+idEvt+"";
			
			PreparedStatement st  = con.prepareStatement(query);
			st.setString(1, evt.getNomeEvt());
			st.setDate(2, evt.getDataInizio());
			st.setDate(3,evt.getDataFine());
			st.setString(4, evt.getBando());
			st.setString(5, evt.getUrl());
			
			st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);
			
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * it's used to remove an Event from the system 
	 * 
	 * @param evt - The Event to remove
	 * 
	 */
	public void eliminaEvento(Evento evento) {
		try {
			//Crea una connessione al DB
			Connection con = DBConnectionPool.getConnection();
			
			int id = evento.getId_evento();

			String query="DELETE FROM Evento WHERE id_evento = ?";
			
			PreparedStatement st = con.prepareStatement(query);
            
            st.setInt(1, id);
            
            st.executeUpdate();
			con.commit();
			st.close();
			DBConnectionPool.releaseConnection(con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	

}
